/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package model;

import java.beans.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author mosta
 */
public class ProductDataBase {
 
    private Connection conn;
    private Statement stmt;
    private ResultSet result;
    private PreparedStatement prepStmt;

    public ProductDataBase() {
        
        DatabaseConnection connection = new DatabaseConnection();
        conn = connection.startConnection();
    }
    
    public int fillProduct(ProductBean product)
    {
        int value=0;
        String chkSql="select p_id from product where p_id=? ";
        String sql="insert into product"
                +"(p_id,p_name,desc,price,p_img,cat_p_id)"
                +"VALUES (?,?,?,?,?,?)";
        try {
            prepStmt = conn.prepareStatement(chkSql);
            prepStmt.setInt(1,product.getpId());
            result = prepStmt.executeQuery();
            if(result.next())
            {
                return value;
            }
            else
            {
                prepStmt = conn.prepareStatement(sql);
                prepStmt.setInt(1, product.getpId());
                prepStmt.setString(2, product.getpName());
                prepStmt.setString(3, product.getDesc());
                prepStmt.setInt(4, product.getPrice());
                prepStmt.setString(5, product.getpImg());
                prepStmt.setInt(6, product.getCatPId());
                value = prepStmt.executeUpdate();
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseConnection.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return value;
    }
    public ArrayList<ProductBean> getLatestProduct(){
       ArrayList<ProductBean> product= new ArrayList<ProductBean>();
        String sql="select * from product order by date desc limit 8";
        try {
            prepStmt=conn.prepareStatement(sql);
            result=prepStmt.executeQuery();
            while(result.next()){
               ProductBean pBean=new ProductBean();
                pBean.setpId(result.getInt("p_id"));
                pBean.setpName(result.getString("p_name"));
                pBean.setPrice(result.getInt("price"));
                pBean.setDesc(result.getString("desc"));
                pBean.setpImg(result.getString("p_img"));
                product.add(pBean);
            }
        } catch (SQLException ex) {
            Logger.getLogger(CategoryDataBase.class.getName()).log(Level.SEVERE, null, ex);
        }
        return product;
    }
    public ProductBean getProductById(int id){
        ProductBean pBean= new ProductBean();
        String sql="select * from product where p_id=?";
        try {
            prepStmt=conn.prepareStatement(sql);
            prepStmt.setInt(1,id);
            result=prepStmt.executeQuery();
            while(result.next()){
                pBean.setpId(result.getInt("p_id"));
                pBean.setpName(result.getString("p_name"));
                pBean.setPrice(result.getInt("price"));
                pBean.setDesc(result.getString("desc"));
                pBean.setpImg(result.getString("p_img"));
                
            }
        } catch (SQLException ex) {
            Logger.getLogger(CategoryDataBase.class.getName()).log(Level.SEVERE, null, ex);
        }
        return pBean;
        
    }
}
